﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private DataTable dt_rep = new DataTable();
        private delegate void Deleg();
        private Deleg del;
        private DataTable pharm = new DataTable();
        private DataTable empl = new DataTable();
        private DataTable dt_pharm;
        private DataTable dt_empl;
        private DateTime date_b;                        //- дата начала отчёта
        private DateTime date_e;                        //- дата окончания отчёта
        private DataTable dt_oon = new DataTable();     //- справочник оон
        private int flag_type;                          //- флаг типа отчёта, 0 - по количеству чеков, 1 - по доле чеков, 2 - по продажам осн товаров, 3 - по продажам доп товаров
        private int flag_as;                            //- флаг, вывод результата по сотрудникам или аптекам, по умолчанию - аптеки
        private int flag_days;                          //- флаг разбивки по дням, по умолчанию 0 - без разбивки, 1 - с разбивкой 
        private int flag_drugs = 0;                     //- флаг по товарам, по умолчанию 0 - без разбивки по товарам, 1 - с разбивкой, 2 - только по товарам
        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            this.Text = "Отчёт по карточкам ООН";
            listBox1.Items.Add("(Все аптеки)");
            listBox2.Items.Add("(Все сотрудники)");
            dateTimePicker1.Value = DateTime.Now.AddDays(-10).Date;
            dateTimePicker2.Value = DateTime.Now.AddDays(-1).Date;
            date_b = dateTimePicker1.Value;
            date_e = dateTimePicker2.Value;
            radioButton1.Checked = true;
            radioButton2.Checked = false;
            pharm = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_PharmSel", 300, null);
            empl = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_EmplSel", 300, null);
            foreach (DataColumn dc in pharm.Columns)
            {
                if (dc.ColumnName != namechcol)
                    dc.ReadOnly = true;
                else
                    dc.ReadOnly = false;
            }
            foreach (DataColumn dc in empl.Columns)
            {
                if (dc.ColumnName != namechcol)
                    dc.ReadOnly = true;
                else
                    dc.ReadOnly = false;
            }
            flag_as = 0;
            flag_days = 0;
            flag_type = 0;
            flag_drugs = 0;
            checkBox1.Checked = true;
            checkBox2.Checked = false;
            checkBox3.Checked = false;
            checkBox4.Enabled = false;
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dt_rep.Clear();
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
            if (flag_type == 1 && flag_as == 0 && flag_drugs != 2)
            {
                dataGridView1.Invoke((Func<object>)(() => dataGridView1.Columns[7].DefaultCellStyle.Format = "p"));
            }
            else if (flag_type == 1 && flag_as == 1 && flag_drugs != 2)
            {
                dataGridView1.Invoke((Func<object>)(() => dataGridView1.Columns[9].DefaultCellStyle.Format = "p"));
            }
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            //try
            //{
                if (dt_pharm == null)
                {
                    dt_pharm = new DataTable();
                    dt_pharm.Columns.Add();
                    foreach (DataRow row in pharm.Rows)
                    {
                        dt_pharm.Rows.Add(row["Код"]);
                    }
                }
                //if (dt_empl == null)
                //{
                //    dt_empl = new DataTable();
                //    dt_empl.Columns.Add();
                //    dt_empl.Columns.Add();
                //    foreach (DataRow row in empl.Rows)
                //    {
                //        dt_empl.Rows.Add(row["Код сотрудника"], row["База"]);
                //    }
                //}
                
                SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[7];
                //MessageBox.Show(flag_as.ToString() + ' ' + flag_days.ToString() + ' ' + flag_drugs.ToString() + ' ' + @flag_type); 
                par[0].name = "@date_b";
                par[0].value = date_b;
                par[1].name = "@date_e";
                par[1].value = date_e;
                par[2].name = "@flag_type";
                par[2].value = flag_type;
                par[3].name = "@flag_as";
                par[3].value = flag_as;
                par[4].name = "@flag_drugs";
                par[4].value = flag_drugs;
                par[5].name = "@pharm";
                par[5].value = dt_pharm;
                par[6].name = "@employ";
                if( dt_empl == null)
                {
                    par[6].value = null;
                }
                else 
                {
                par[6].value = dt_empl;
                }

                if (flag_days == 0)
                {
                    dt_rep = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_OON_URS_withoutdays", 300, par);
                }
                else if (flag_days == 1)
                {
                    dt_rep = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_OON_URS", 300, par);
                }
                conn.Close();
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.ToString());
            //    conn.Close();
            //}

        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;

            

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //- Фильтр по аптекам
            dt_pharm = null;
            dt_pharm = new DataTable();
            dt_pharm.Columns.Add();
            try
            {
                PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();

                if (pharm == null)
                {
                    SqlCommand myCommand = conn.CreateCommand();
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = @"pas_PharmSel";
                    if (conn.State == ConnectionState.Closed) conn.Open();
                    pharm = new DataTable();
                    pharm.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in pharm.Columns)
                    {
                        if (dc.ColumnName != namechcol) 
                            dc.ReadOnly = true;
                        else
                            dc.ReadOnly = false;
                    }
                    conn.Close();
                }
                f3.namechcol = namechcol;
                f3.Text = "Фильтр по аптекам";
                f3.sortcoumn = "Аптека";
                f3.dataGridView1.DataSource = pharm.DefaultView;
                f3.ShowDialog();
                listBox1.Items.Clear();
                foreach (DataRow dr in pharm.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]) == true)
                    {
                        dt_pharm.Rows.Add(dr["Код"]);
                        listBox1.Items.Add(dr[f3.sortcoumn]);
                    }
                }
                if (listBox1.Items.Count == 0) listBox1.Items.Add("(Все аптеки)");
                f3.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            //- Дата начала отчёта
            date_b = dateTimePicker1.Value;
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            //- Дата окончания отчёта
            date_e = dateTimePicker2.Value;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            //- Фильтр по сотрудникам
            dt_empl = null;
            dt_empl = new DataTable();
            dt_empl.Columns.Add();
            dt_empl.Columns.Add();
            try
            {
                PharmFilter.PharmFilter f5 = new PharmFilter.PharmFilter();

                if (empl == null)
                {
                    SqlCommand myCommand = conn.CreateCommand();
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = @"pas_EmplSel";
                    if (conn.State == ConnectionState.Closed) conn.Open();
                    empl = new DataTable();
                    empl.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in empl.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                    conn.Close();
                }
                f5.namechcol = namechcol;
                f5.Text = "Фильтр по сотрудникам";
                f5.sortcoumn = "Фамилия Имя Отчество";
                f5.dataGridView1.DataSource = empl.DefaultView;
                f5.ShowDialog();
                listBox2.Items.Clear();
                foreach (DataRow dr in empl.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]) == true)
                    {
                        dt_empl.Rows.Add(dr["Код сотрудника"], dr["База"]);
                        listBox2.Items.Add(dr[f5.sortcoumn]);
                    }
                }
                if (listBox2.Items.Count == 0) listBox2.Items.Add("(Все сотрудники)");
                f5.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            //- Отчет по количеству чеков
            if (radioButton1.Checked == true)
            {
                flag_type = 0;
                checkBox4.Checked = false;
                checkBox4.Enabled = false;
            }
        }

        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            //- Отчёт по доле чеков
            if (radioButton2.Checked == true)
            {
                flag_type = 1;
                checkBox4.Checked = false;
                checkBox4.Enabled = false;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            ExpToExcel ex = new ExpToExcel();
            ex.SaveDGVToCSVfile("отчёт по ООН", dataGridView1, true);
        }

        private void radioButton3_CheckedChanged(object sender, EventArgs e)
        {
            //- Отчёт по продажам основных товаров
            if (radioButton3.Checked)
            {
                flag_type = 2;
                checkBox4.Enabled = true;
            }
        }

        private void radioButton4_CheckedChanged(object sender, EventArgs e)
        {
            //- Отчёт по продажам дополнительных товаров
            if (radioButton4.Checked)
            {
                flag_type = 3;
                checkBox4.Enabled = true;
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            //- Вывести справочник ООН
            dt_oon = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_OONSel", 30, null);
            dataGridView1.DataSource = dt_oon.DefaultView;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            //- Вывод результата по аптекам
            if (checkBox1.Checked == true)
            {
                flag_as = 0;
                checkBox2.Checked = false;
                checkBox5.Checked = false;
            }
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            //- Вывод результата по сотрудникам
            if (checkBox2.Checked == true)
            {
                flag_as = 1;
                checkBox1.Checked = false;
                checkBox5.Checked = false;
            }

        }

        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            //- С разбивкой по дням
            if (checkBox3.Checked == true)
            {
                flag_days = 1;
            }
            else if (checkBox3.Checked == false)
            {
                flag_days = 0;
            }
        }

        private void checkBox4_CheckedChanged(object sender, EventArgs e)
        {
            //- Товары, 0 - без разбивки, 1 - с разбивкой
            if (checkBox4.Checked == true)
            {
                flag_drugs = 1;
            }
            else if (checkBox4.Checked == false)
            {
                flag_drugs = 0;
            }
        }

        private void checkBox5_CheckedChanged(object sender, EventArgs e)
        {
            //- Только по товарам
            if (checkBox5.Checked == true)
            {
                flag_drugs = 2;
                checkBox4.Checked = false;
                checkBox1.Checked = false;
                checkBox2.Checked = false;
            }
            else
            {
                flag_drugs = 0;
                checkBox4.Checked = false;
                checkBox1.Checked = true;
            }

        }

  
    }
}
